﻿CREATE TRIGGER [dbo].[Vanzare_Stoc_Before_Trigger]
   ON [dbo].[Vanzare]
   INSTEAD OF INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @stocTotalMedicament float = 0;
	DECLARE @dataCurenta datetime = GETDATE();
	
	IF EXISTS(
		SELECT
			1
		FROM 
			(SELECT i.Id_Medicament,SUM(i.Cantitate) AS Cantitate
			 FROM inserted i
			 GROUP BY Id_Medicament) medicament_vandut
			 
			 left outer join
			 
			(SELECT s.Id_Medicament,SUM(s.Cantitate_Actuala) as Cantitate_Actuala
			 FROM Stoc s
			 WHERE s.Data_Intrare <= @dataCurenta AND s.Data_Expirare > @dataCurenta
			 GROUP BY S.Id_Medicament) stoc_medicament
			 
			 ON 
			 medicament_vandut.Id_Medicament = stoc_medicament.Id_Medicament
			 
		WHERE 
			medicament_vandut.Cantitate > stoc_medicament.Cantitate_Actuala OR
			stoc_medicament.Cantitate_Actuala is null
	)
	
	BEGIN
		RAISERROR (N'Nu exista produse in stoc.', 17, 1); 
		RETURN;
	END
	
	INSERT INTO VANZARE
	(
		Id_Medicament
		,Cantitate
		,Pret
		,Data
	)
	SELECT 
		Id_Medicament
		,Cantitate
		,Pret
		,Data
	FROM inserted
	
	select Id_Vanzare from [dbo].[VANZARE] where @@ROWCOUNT > 0 and Id_Vanzare = scope_identity();
END




